<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed'); 

class Check extends CI_Model {

        /*
         * TODO: Проверка объектов(СНП)
         */
    var $role_arr; // Массив ролей
    var $obj_arr; // Массив объектов
    var $category; //Массив категорий

    function __construct()
    {
            parent::__construct();
	    $this->role_arr['admin'] = '884';
            $this->role_arr['moderator'] = '757';
            $this->role_arr['attendee'] = '758';
            
            //$this->obj_arr['null'] = 'null';

            $this->category['user'] = 1;
            $this->category['out'] = 3;
    }

    function checkVVSUAccount($login, $pass, $role = 'moderator', $obj = 'null')
    {
	    $CI = &get_instance();
	    if($CI->env == 'development'){
		if($login == 'admtest')
			{
				$data['UID'] = '1';
				$data['firstname'] = 'Серванта';
				$data['surname'] = 'Админ';
				$data['secondname'] = 'Всея';
				$data['role'] = 'admin';
				return $data;
			}
		else if($login == 'modertest')
			{
				$data['UID'] = '2';
				$data['firstname'] = 'Комнаты';
				$data['surname'] = 'Модератор';
				$data['secondname'] = 'Всея';
				$data['role'] = 'moderator';
				return $data;
			}
		else if($login == 'attest')
			{
				$data['UID'] = '3';
				$data['firstname'] = 'Вебинара';
				$data['surname'] = 'Слушатель';
				$data['secondname'] = 'Всея';
				$data['role'] = 'attendee';
				return $data;
			}
	    }
        $db = $CI->load->database('webdb', TRUE);
        $query = $db->query('EXEC [Users].[dbo].[ECheckLoginAndPassword] \''.$login.'\' , \''.$pass.'\'');
        $CLAP = $query->row(1);
        if($CLAP->status > 0)
        {
                $query = $db->query('EXECUTE[AUTH].[dbo].[CHECK_RIGHTS] '.
                        $CLAP->status.','.$this->role_arr[$role].','.$obj);
                $ChR = $query->row(1);
                if($ChR->STATUS == 0)
                {
                        return -3;
                }
                // data
                $query = $db->query('SELECT surname, firstname, secondname FROM [Users]..[rgh_users] WHERE login = \''.
                        $login.'\'');
                $data = $query->result_array();
                $data[0]['UID'] = $CLAP->status;
                $data[0]['firstname'] = mb_convert_encoding($data[0]['firstname'], 'utf8', 'cp1251');
                $data[0]['surname'] = mb_convert_encoding($data[0]['surname'], 'utf8', 'cp1251');
                $data[0]['secondname'] = mb_convert_encoding($data[0]['secondname'], 'utf8', 'cp1251');
                $data[0]['role'] = $role;//array_search($ChR->STATUS, $this->role_arr); //$ChR->STATUS;
                // end data
                return $data[0];
        }
        else return $CLAP->status;
    }
    
    function checkObjRights($id, $uid)
    {
	$CI = &get_instance();
	$db = $CI->load->database('webdb', TRUE);
	$query = $db->query('EXECUTE[AUTH].[dbo].[CHECK_RIGHTS] '.$uid.',758,'.$id);
        $ChR = $query->row(1);
	if($ChR->STATUS == 0)
        {
		return -3;
	}
	return 1;
    }
    
    function checkUserCat($id, $cat = 'out')
    {
	    $CI = &get_instance();
	    $db = $CI->load->database('webdb', TRUE);
	    $query = $db->query("select * from users..rgh_usercategories where id_user = ".$id." and  id_category = ".$this->category[$cat]);
	    $cnt = $query->num_rows();
	    if($cnt > 0) return true;
	    return false;
    }
    
    function getUserByID($UID)
    {
		$CI =&get_instance();
		$db = $CI->load->database('webdb', TRUE);
		$query = $db->query('SELECT surname, firstname, secondname FROM [Users]..[rgh_users] WHERE ID_User = \''.
                        $UID.'\'');
		$data = $query->result_array();
		$data[0]['firstname'] = mb_convert_encoding($data[0]['firstname'], 'utf8', 'cp1251');
                $data[0]['surname'] = mb_convert_encoding($data[0]['surname'], 'utf8', 'cp1251');
                $data[0]['secondname'] = mb_convert_encoding($data[0]['secondname'], 'utf8', 'cp1251');
		return $data;
    }

    function getUsers($cat = 'user', $from = 0, $cnt = 10)
    {
            /*
             * WITH num_row
             * AS
             * (
             * SELECT row_number() OVER (ORDER BY id) as nom , *
             * FROM TABLE
             * )
             * SELECT * FROM num_row
             * WHERE nom BETWEEN (n - m) AND n
             * n — номер последнего элемента
             * m — необходимое количество элементов
             * id — уникальный идентификатор
             */
            $from += $cnt;
            $q = 'select top 20 * from users..rgh_users a
                inner join users..rgh_usercategories b on a.id_user = b.id_user and b.id_category = '.$this->category[$cat];

            $q2 = 'WITH num_row AS
                   (SELECT row_number() OVER (ORDER BY a.id_user) as nom, a.*
                    FROM users..rgh_users a
                    INNER JOIN users..rgh_usercategories b ON a.id_user = b.id_user AND b.id_category = '.$this->category[$cat].'
                   )
                   SELECT * FROM num_row WHERE nom BETWEEN ('.$from.' - 10) AND '.$from;

            $CI =&get_instance();
            $db = $CI->load->database('webdb', TRUE);
            $query = $db->query($q2);
            $data = $query->result_array();
            $i = 0;
            while(isset($data[$i]))
            {
                    $data[$i]['FirstName'] = mb_convert_encoding($data[$i]['FirstName'], 'utf8', 'cp1251');
                    $data[$i]['Surname'] = mb_convert_encoding($data[$i]['Surname'], 'utf8', 'cp1251');
                    $data[$i]['SecondName'] = mb_convert_encoding($data[$i]['SecondName'], 'utf8', 'cp1251');
                    $i++;
            }
            return $data;
    }

    function getUsersCount($cat = 'user')
    {
            $CI =&get_instance();
            $db = $CI->load->database('webdb', TRUE);
            $db->where('b.id_category', $this->category[$cat]);
            $db->from('users..rgh_users a');
            $db->join('users..rgh_usercategories b', 'a.id_user = b.id_user', 'inner');
            return $db->count_all_results();
    }

    function getVVSUInsts()
    {
            $CI =&get_instance();
            $db = $CI->load->database('webdb', TRUE);
            $q = "select distinct fg.ID  as id, fg.DESCR as name
                        from FELIX_REP.dbo.DIVISION fg
                        inner join FELIX_REP.dbo.pln_EduProg EP with (nolock) on EP.id_Institute=fg.ID
                        inner join FELIX_REP..DIVISION_ROLE_LINK c on
                        c.BU_ID=fg.BU_ID and c.DIV_ID = fg.ID and fg.BU_ID='0001'
                        where EP.rip=0 and c.ROLE_ID='".mb_convert_encoding('Инс', 'cp1251', 'utf8')."'";

            $query = $db->query($q);
            $data = $query->result_array();
            $i = 0;
            while(isset($data[$i]))
            {
                    $data[$i]['name'] = mb_convert_encoding($data[$i]['name'], 'utf8', 'cp1251');
                    $i++;
            }
            return $data;
    }

    function getVVSUKafs($InstID)
    {
            $CI =&get_instance();
            $db = $CI->load->database('webdb', TRUE);
            $q = "declare @InstID as varchar(4)
                set @InstID='".$InstID."'
                select distinct fg.ID as id, fg.NAME as name from FELIX_REP.dbo.DIVISION fg with (nolock) inner join FELIX_REP.dbo.pln_EduProg EP with (nolock) on EP.id_Kafedra=fg.ID
                inner join FELIX_REP..DIVISION_ROLE_LINK c on
                c.BU_ID=fg.BU_ID and c.DIV_ID = fg.ID and fg.BU_ID='0001'
                where EP.rip=0
                --and ((len(@InstID)<=0) or cast(EP.id_Institute as varchar)=@InstID)
                and cast(EP.id_Institute as varchar)=@InstID--'1001'
                and c.ROLE_ID='".mb_convert_encoding('Каф', 'cp1251', 'utf8')."'";
            $query = $db->query($q);
            $data = $query->result_array();
            $i = 0;
            while(isset($data[$i]))
            {
                    $data[$i]['name'] = mb_convert_encoding($data[$i]['name'], 'utf8', 'cp1251');
                    $i++;
            }
            return $data;
    }

    function getVVSUGroups($KafID)
    {
            $CI =&get_instance();
            $db = $CI->load->database('webdb', TRUE);
            $q = "select PGS.id_Group as id, PGS.Name as name
                from FELIX_REP.dbo.pln_EduProg EP
                inner join FELIX_REP.dbo.pln_EduProgYear EPY on EP.id_Dop=EPY.id_Dop
                inner join FELIX_REP.dbo.pln_group_Stud PGS on EPY.id_Pln = PGS.id_Pln
                where EP.Rip = 0
                --and id_Institute=1000
                and id_Kafedra=".$KafID."
                and PGS.rip = 0
                and Year>=".(date("Y",time())-5);
            $query = $db->query($q);
            $data = $query->result_array();
            $i = 0;
            while(isset($data[$i]))
            {
                    $data[$i]['name'] = mb_convert_encoding($data[$i]['name'], 'utf8', 'cp1251');
                    $i++;
            }
            $data['count'] = $i;
            return $data;
    }

    function getVVSUStuds($GroupID)
    {
            $CI =&get_instance();
            $db = $CI->load->database('webdb', TRUE);
            $q = "SELECT a.id_People as id,FIO2 as name FROM [FELIX_REP].[dbo].[PEOPLE_PLN] a
                inner join FELIX_REP..PEOPLE_LIST b on a.id_People=b.id_People
                where id_Group = ".$GroupID." AND id_Status = 2 ORDER BY name";
            $query = $db->query($q);
            $data = $query->result_array();
            $i = 0;
            while(isset($data[$i]))
            {
                    $data[$i]['name'] = mb_convert_encoding($data[$i]['name'], 'utf8', 'cp1251');
                    $i++;
            }
            return $data;
    }
}

/* Институты
select distinct fg.ID  as InstID, fg.DESCR as InstName
    from FELIX_REP.dbo.DIVISION fg
    inner join FELIX_REP.dbo.pln_EduProg EP with (nolock) on EP.id_Institute=fg.ID

    inner join FELIX_REP..DIVISION_ROLE_LINK c on
    c.BU_ID=fg.BU_ID and c.DIV_ID = fg.ID and fg.BU_ID='0001'
    where EP.rip=0 and c.ROLE_ID='Инс'
*/
/**
Кафедра

declare @InstID as varchar(4)
set @InstID='1001'  //Сюда код института
select distinct fg.ID as KafID, fg.NAME as KafName from FELIX_REP.dbo.DIVISION fg with (nolock) inner join FELIX_REP.dbo.pln_EduProg EP with (nolock) on EP.id_Kafedra=fg.ID

    inner join FELIX_REP..DIVISION_ROLE_LINK c on
    c.BU_ID=fg.BU_ID and c.DIV_ID = fg.ID and fg.BU_ID='0001'


where EP.rip=0
--and ((len(@InstID)<=0) or cast(EP.id_Institute as varchar)=@InstID)
and cast(EP.id_Institute as varchar)=@InstID--'1001'
and c.ROLE_ID='Каф'
**/
/*Группа
select PGS.id_Group, PGS.Name as GroupName
from FELIX_REP.dbo.pln_EduProg EP
inner join FELIX_REP.dbo.pln_EduProgYear EPY on EP.id_Dop=EPY.id_Dop
inner join FELIX_REP.dbo.pln_group_Stud PGS on EPY.id_Pln = PGS.id_Pln
where EP.Rip = 0
--and id_Institute=1001 and id_Kafedra=100103
and PGS.rip = 0
--and Year=2010
*/
/*Список студентов в группе
SELECT a.id_People,FIO2 FROM [FELIX_REP].[dbo].[PEOPLE_PLN] a
inner join FELIX_REP..PEOPLE_LIST b on a.id_People=b.id_People

where id_Group = 15676 //Группа Id_group
*/
 